package com.hw.dagl.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.hw.dagl.model.Dagl_T_Cjtj;
import com.hw.dagl.model.Vehicle;
import com.hw.dagl.service.HomebtService;
import com.hw.dao.utils.EntityTools;
import com.hw.dao.utils.OraclePageDaoBase;
import com.hw.dao.utils.Page;
import com.hw.utils.StringUtils;
/**
 * 首页饼图分析
 * @author Administrator
 *
 */

@Service
public class HomebtServiceImpl implements HomebtService {
 
	@Resource
	private OraclePageDaoBase oraclePageDaoBase;

	
	//采集数据统计
	@Override
	public List<Dagl_T_Cjtj> cjcount(String tjld,String glbm) {
		StringBuffer sql1=new StringBuffer("");
		if(!glbm.equals("340000000400")){ //总队车管所
			sql1=sql1.append("select sum(t.ycjtotal) ycjTotal,t.bmmc from VEH_RECORD_COUNT t  where 1=1 ");
			if(StringUtils.isNotEmpty(tjld)){
				String tjsjStr=	 lidu(tjld,"t.cjsj");
						sql1.append(tjsjStr);
			}
			sql1.append("  group by t.bmmc ");
		}else{
			sql1=sql1.append("select sum(t.ycjtotal) ycjTotal,r.bmmc from VEH_RECORD_COUNT t,FRM_DEPARTMENT_LOCAL r where t.ssds=r.glbm ");
			if(StringUtils.isNotEmpty(tjld)){
				String tjsjStr=	 lidu(tjld,"t.cjsj");
						sql1.append(tjsjStr);
			}
			sql1.append("  group by r.bmmc ");
		}
		List<Dagl_T_Cjtj> listCjcount=oraclePageDaoBase.query(sql1.toString(),new RowMapper<Dagl_T_Cjtj>(){
					private EntityTools<Dagl_T_Cjtj> et = new EntityTools<Dagl_T_Cjtj>();
					@Override
					public Dagl_T_Cjtj mapRow(ResultSet rs, int arg1)throws SQLException {
						Dagl_T_Cjtj sx = new Dagl_T_Cjtj();
						et.setColumnsValueByResultSet(rs, sx);
						return sx;
					}
				});				
	return listCjcount;
	}

	/**
  	 * 首页查询影像采集列表
  	 */
	@Override
	public Page<Vehicle> findYxcjxx(Page<Vehicle> pages, Vehicle info,
			String startTime, String endTime) {
		 StringBuffer sql =new StringBuffer("select t.*,csys.dmsm1 csysmc,hp.dmsm1 hpzlmc,'长:'||t.cwkc||' 宽:'||t.cwkk||' 高:'||t.cwkg wkccmc "
				+ ",substr(t.fzjg,0,1)||t.hphm hphmmc from VEHICLE_local t  "
				+ " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1007' and t.xtlb='00') hp on hp.dmz=t.hpzl "//机动车号牌种类代码
        		+ " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1008' and t.xtlb='00') csys on csys.dmz=t.csys ");//机动车车身颜色基本色调代码
		  List<Object> args = new ArrayList<Object>();
		 SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");	//where t.yxzt=0 
				sql.append(" where t.ccdjrq >= trunc(sysdate-30) ");
				sql.append(" AND t.ccdjrq <= trunc(sysdate) ");
	        sql.append(" order by ccdjrq desc ");
			return oraclePageDaoBase.queryForPage(sql.toString(), pages,new RowMapper<Vehicle>() {
				private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
				@Override
				public Vehicle mapRow(ResultSet arg0, int arg1) throws SQLException {
					Vehicle veh = new Vehicle();
					et.setColumnsValueByResultSet(arg0, veh);
					return veh;
				}
			}, args.toArray());				
	}
	/*//违法采集饼状图
	@Override
	public List<Djgl_T_GcwfHSSR> wfcjcounts(String tjld) {
		String sql=" select count(1) count,c.sxjg  from (select t.gcbh,t.sxjg from DJGL_T_GCWF_HSSR t where 1=1 ";
		if(StringUtils.isNotEmpty(tjld)){
			String tjsjStr = wfsxserviceImpl.lidu(tjld,"t.wfsj");
			sql+=tjsjStr;
		}
				sql+= " ) c group by c.sxjg order by count ";
		List<Djgl_T_GcwfHSSR> list1= oraclePageDaoBase.query(sql,new RowMapper<Djgl_T_GcwfHSSR>(){
			private EntityTools<Djgl_T_GcwfHSSR> et = new EntityTools<Djgl_T_GcwfHSSR>();
			@Override
			public Djgl_T_GcwfHSSR mapRow(ResultSet rs, int arg1)throws SQLException {
				Djgl_T_GcwfHSSR cy = new Djgl_T_GcwfHSSR();
				et.setColumnsValueByResultSet(rs, cy);
				return cy;
			}
		});
	return list1;
	}

	//违法审核饼状图
	@Override
	public List<Djgl_T_GcwfHSSR> wfshcounts(String tjld) {
		String sql=" select count(1) count,c.shbz  from (select t.gcbh,t.shbz from DJGL_T_GCWF_HSSR t where 1=1  and t.sxjg='1' ";
				if(StringUtils.isNotEmpty(tjld)){
					String tjsjStr = wfsxserviceImpl.lidu(tjld,"t.wfsj");
					sql+=tjsjStr;
				}
				sql+= " ) c group by c.shbz order by count ";
				System.out.println(sql);
		List<Djgl_T_GcwfHSSR> list1= oraclePageDaoBase.query(sql,new RowMapper<Djgl_T_GcwfHSSR>(){
			private EntityTools<Djgl_T_GcwfHSSR> et = new EntityTools<Djgl_T_GcwfHSSR>();
			@Override
			public Djgl_T_GcwfHSSR mapRow(ResultSet rs, int arg1)throws SQLException {
				Djgl_T_GcwfHSSR sh = new Djgl_T_GcwfHSSR();
				et.setColumnsValueByResultSet(rs, sh);
				return sh;
			}
		});
	return list1;
	}

	
	//违法入库饼状图
	@Override
	public List<Djgl_T_GcwfHSSR> wfrkcounts(String tjld) {
		String sql=" select count(1) count,c.rkbz  from (select t.gcbh,t.rkbz from DJGL_T_GCWF_HSSR t where 1=1 "
				+ " and t.sxjg='1' and (t.shbz='1' or t.shbz='3') ";
				if(StringUtils.isNotEmpty(tjld)){
					String tjsjStr = wfsxserviceImpl.lidu(tjld,"t.wfsj");
					sql+=tjsjStr;
				}
				sql+= "  ) c group by c.rkbz order by count ";
		List<Djgl_T_GcwfHSSR> list1= oraclePageDaoBase.query(sql,new RowMapper<Djgl_T_GcwfHSSR>(){
			private EntityTools<Djgl_T_GcwfHSSR> et = new EntityTools<Djgl_T_GcwfHSSR>();
			@Override
			public Djgl_T_GcwfHSSR mapRow(ResultSet rs, int arg1)throws SQLException {
				Djgl_T_GcwfHSSR rk = new Djgl_T_GcwfHSSR();
				et.setColumnsValueByResultSet(rs, rk);
				return rk;
			}
		});
	return list1;
	}*/
	
	  public String lidu(String n, String tjsjStr) {
		    String sql = "";
		    Calendar cal = Calendar.getInstance();
		    SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd");
		    if (n.equals("1")) {
		    	cal.add(Calendar.DATE, -1);
		        sql=" and "+tjsjStr+">"+"to_date('"+date.format(cal.getTime())+"','yyyy-mm-dd')"+" and "+tjsjStr+"<="+" to_date('"+date.format(new Date())+"','yyyy-mm-dd') "  ;
		    } else if (n.equals("2")) {		
		      cal.add(Calendar.DATE, -7);
		        sql=" and "+tjsjStr+">"+"to_date('"+date.format(cal.getTime())+"','yyyy-mm-dd')"+" and "+tjsjStr+"<="+" to_date('"+date.format(new Date())+"','yyyy-mm-dd') "  ;
		    } else if (n.equals("3")) {
		    	cal.add(Calendar.MONTH, -1);
		        sql=" and "+tjsjStr+">"+"to_date('"+date.format(cal.getTime())+"','yyyy-mm-dd')"+" and "+tjsjStr+"<="+" to_date('"+date.format(new Date())+"','yyyy-mm-dd') "  ;
		    }
		    return sql;
		  }
	
	
	
	
	
}
